﻿using FinancialReportPowerPointAddIn.Helper;
using Microsoft.Office.Interop.PowerPoint;
using System.Drawing;
using System.Linq;


namespace FinancialReportPowerPointAddIn.PPTModule
{
    public static class GenerateMTDProfitDetail
    {
        public static void Write(int year, int month, int comYear, int comMonth)
        {
            //获取当前ppt中所有的幻灯片
            var slides = Globals.ThisAddIn.Application.ActivePresentation.Slides;
            //在当前选中幻灯片前插入新的幻灯片--代码提示中没有F12进去才找到的方法
            Slide currentslide = slides.Add(slides.Count + 1, PpSlideLayout.ppLayoutBlank);
            currentslide.Select();
            //写入标题           
            Shape titleBox = currentslide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, 30, 30, 370, 30);
            titleBox.TextFrame.TextRange.Text = $"StoreProfitSummary{year}{month.ToString("00")}VS{comYear}{comMonth.ToString("00")}";
            titleBox.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoCTrue;
            //写入表格            
            Shape tableShape = currentslide.Shapes.AddTable(10, 18, 0, 75, currentslide.Master.Width, currentslide.Master.Height - 96);
            Table table = tableShape.Table;
            // 编写标题行
            //写入MTD  要调转颜色RGB =>BGR          
            table.Cell(1, 1).Shape.TextFrame.TextRange.Text = "MTD";            
            table.Cell(1, 1).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            table.Cell(1, 1).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb(); 
            table.Cell(1, 1).Shape.Fill.ForeColor.RGB = Color.FromArgb(255, 255, 255).ToArgb();
            table.Cell(1, 1).Merge(tableShape.Table.Cell(2, 1));
            BorderHelper.SetRightBorder(table.Cell(1, 1));
            //写入TotalSales
            table.Cell(1, 2).Shape.TextFrame.TextRange.Text = $"TotalVs{comYear.ToString().Substring(2)}\r\nComps";
            table.Cell(1, 2).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            table.Cell(1, 2).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb(); 
            table.Cell(1, 2).Shape.Fill.ForeColor.RGB = Color.FromArgb(144, 238, 144).ToArgb();
            table.Cell(1, 2).Merge(tableShape.Table.Cell(1, 3));

            table.Cell(2, 2).Shape.TextFrame.TextRange.Text = "Total\r\nSales";
            table.Cell(2, 2).Shape.Fill.ForeColor.RGB = Color.FromArgb(144, 238, 144).ToArgb();

            table.Cell(2, 3).Shape.TextFrame.TextRange.Text = "Total\r\nGC";
            table.Cell(2, 3).Shape.Fill.ForeColor.RGB = Color.FromArgb(144, 238, 144).ToArgb();
            BorderHelper.SetRightBorder(table.Cell(1, 3));
            BorderHelper.SetRightBorder(table.Cell(2, 3));
            //产品Sales
            table.Cell(1, 4).Shape.TextFrame.TextRange.Text = "产品Sales";
            table.Cell(1, 4).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            table.Cell(1, 4).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb(); 
            table.Cell(1, 4).Shape.Fill.ForeColor.RGB = Color.FromArgb(193, 182, 255).ToArgb();
            table.Cell(1, 4).Merge(tableShape.Table.Cell(1, 7));
            BorderHelper.SetRightBorder(table.Cell(1, 4));

            table.Cell(2, 4).Shape.TextFrame.TextRange.Text = $"Vs{comYear.ToString().Substring(2)}\r\nComps";
            table.Cell(2, 4).Shape.Fill.ForeColor.RGB = Color.FromArgb(193, 182, 255).ToArgb();

            table.Cell(2, 5).Shape.TextFrame.TextRange.Text = "实际";
            table.Cell(2, 5).Shape.Fill.ForeColor.RGB = Color.FromArgb(193, 182, 255).ToArgb();

            table.Cell(2, 6).Shape.TextFrame.TextRange.Text = "Plan";
            table.Cell(2, 6).Shape.Fill.ForeColor.RGB = Color.FromArgb(193, 182, 255).ToArgb();

            table.Cell(2, 7).Shape.TextFrame.TextRange.Text = "Var%";
            table.Cell(2, 7).Shape.Fill.ForeColor.RGB = Color.FromArgb(193, 182, 255).ToArgb();
            BorderHelper.SetRightBorder(table.Cell(2, 7));

            //非产品Sales
            table.Cell(1, 8).Shape.TextFrame.TextRange.Text = "非产品Sales";
            table.Cell(1, 8).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            table.Cell(1, 8).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, 8).Shape.Fill.ForeColor.RGB = Color.FromArgb(135, 184, 222).ToArgb();
            table.Cell(1, 8).Merge(tableShape.Table.Cell(1, 11));
            BorderHelper.SetRightBorder(table.Cell(1, 8));

            table.Cell(2, 8).Shape.TextFrame.TextRange.Text =$"Vs{comYear.ToString().Substring(2)}\r\nComps";
            table.Cell(2, 8).Shape.Fill.ForeColor.RGB = Color.FromArgb(135, 184, 222).ToArgb();

            table.Cell(2, 9).Shape.TextFrame.TextRange.Text = "实际";
            table.Cell(2, 9).Shape.Fill.ForeColor.RGB = Color.FromArgb(135, 184, 222).ToArgb();

            table.Cell(2, 10).Shape.TextFrame.TextRange.Text = "Plan";
            table.Cell(2, 10).Shape.Fill.ForeColor.RGB = Color.FromArgb(135, 184, 222).ToArgb();

            table.Cell(2, 11).Shape.TextFrame.TextRange.Text = "Var%";
            table.Cell(2, 11).Shape.Fill.ForeColor.RGB = Color.FromArgb(135, 184, 222).ToArgb();
            BorderHelper.SetRightBorder(table.Cell(2, 11));

            //餐厅现金流
            table.Cell(1, 12).Shape.TextFrame.TextRange.Text = "餐厅现金流";
            table.Cell(1, 12).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            table.Cell(1, 12).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb(); 
            table.Cell(1, 12).Shape.Fill.ForeColor.RGB = Color.FromArgb(250, 206, 135).ToArgb();
            table.Cell(1, 12).Merge(tableShape.Table.Cell(1, 14));
            BorderHelper.SetRightBorder(table.Cell(1, 12));

            table.Cell(2, 12).Shape.TextFrame.TextRange.Text = "实际";
            table.Cell(2, 12).Shape.Fill.ForeColor.RGB = Color.FromArgb(250, 206, 135).ToArgb();

            table.Cell(2, 13).Shape.TextFrame.TextRange.Text = "Plan";
            table.Cell(2, 13).Shape.Fill.ForeColor.RGB = Color.FromArgb(250, 206, 135).ToArgb();

            table.Cell(2, 14).Shape.TextFrame.TextRange.Text = "Var%";
            table.Cell(2, 14).Shape.Fill.ForeColor.RGB = Color.FromArgb(250, 206, 135).ToArgb();
            BorderHelper.SetRightBorder(table.Cell(2, 14));

            //再投资
            table.Cell(1, 15).Shape.TextFrame.TextRange.Text = "调整项";
            table.Cell(1, 15).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            table.Cell(1, 15).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb();
            table.Cell(1, 15).Shape.Fill.ForeColor.RGB = Color.FromArgb(255, 255, 255).ToArgb();
            BorderHelper.SetRightBorder(table.Cell(1, 15));

            table.Cell(2, 15).Shape.TextFrame.TextRange.Text = "再投资";
            table.Cell(2, 15).Shape.Fill.ForeColor.RGB = Color.FromArgb(255, 255, 255).ToArgb();
            BorderHelper.SetRightBorder(table.Cell(2, 15));

            //调整后现金流
            table.Cell(1, 16).Shape.TextFrame.TextRange.Text = "调整后现金流";
            table.Cell(1, 16).Shape.TextFrame.TextRange.Font.Bold = Microsoft.Office.Core.MsoTriState.msoTrue;
            table.Cell(1, 16).Shape.TextFrame.TextRange.Font.Color.RGB = Color.FromArgb(0, 0, 0).ToArgb(); 
            table.Cell(1, 16).Shape.Fill.ForeColor.RGB = Color.FromArgb(238, 130, 238).ToArgb();
            table.Cell(1, 16).Merge(tableShape.Table.Cell(1, 18));

            table.Cell(2, 16).Shape.TextFrame.TextRange.Text = "实际";
            table.Cell(2, 16).Shape.Fill.ForeColor.RGB = Color.FromArgb(238, 130, 238).ToArgb();

            table.Cell(2, 17).Shape.TextFrame.TextRange.Text = "Plan";
            table.Cell(2, 17).Shape.Fill.ForeColor.RGB = Color.FromArgb(238, 130, 238).ToArgb();

            table.Cell(2, 18).Shape.TextFrame.TextRange.Text = "Var%";
            table.Cell(2, 18).Shape.Fill.ForeColor.RGB = Color.FromArgb(238, 130, 238).ToArgb();
            //
            //开始写入数据
            string[] restaurants = new string[] { "潭江店", "开大店", "白石店", "西粤店", "东湖店", "恩平店", "油十店", "汇总" };
            int currentrow = 3;
            using (var db = new FinancialReportContext())
            {
                foreach (string item in restaurants)
                {
                    table.Cell(currentrow, 1).Shape.TextFrame.TextRange.Text = item;
                    BorderHelper.SetRightBorder(table.Cell(currentrow, 1));
                    table.Cell(currentrow, 1).Shape.TextFrame.TextRange.Font.Size = 14;
                    string flag = string.Empty;
                    string code = string.Empty;
                    Common.GetPLNameAndCode(item, out flag, out code);
                    //写入TotalSalesComp
                    var cy = db.PLDatas.FirstOrDefault(x => x.Year == year && x.Month == month && x.IsPlan == false && x.Restaurant == flag);
                    var cyplan = db.PLDatas.FirstOrDefault(x => x.Year == year && x.Month == month && x.IsPlan == true && x.Restaurant == flag);
                    var comy = db.PLDatas.FirstOrDefault(x => x.Year == comYear && x.Month == comMonth && x.IsPlan == false && x.Restaurant == flag);
                    var comp = Calculate.CalVar(cy.产品净收入 + cy.非产品净收入, comy.非产品净收入 + comy.产品净收入) ;
                    table.Cell(currentrow, 2).Shape.TextFrame.TextRange.Text = comp.ToString("p");
                    //写入TotalGC 
                    if (item == "汇总")
                    {
                        var cysgc = db.PlatformSales.Where(x => x.Year == year && x.Month == month).Sum(x => x.全店GC);
                        var comysgc = db.PlatformSales.Where(x => x.Year == comYear && x.Month == comMonth).Sum(x => x.全店GC);
                        comp = Calculate.CalVar(cysgc, comysgc);
                    }
                    else
                    {
                        var cys = db.PlatformSales.FirstOrDefault(x => x.Year == year && x.Month == month && x.Uscode == code);
                        var comys = db.PlatformSales.FirstOrDefault(x => x.Year == comYear && x.Month == comMonth && x.Uscode == code);
                        comp = Calculate.CalVar(cys.全店GC, comys.全店GC);
                    }
                    table.Cell(currentrow, 3).Shape.TextFrame.TextRange.Text = comp.ToString("p");
                    BorderHelper.SetRightBorder(table.Cell(currentrow, 3));
                    //写入产品Vs19Comps
                    comp =  Calculate.CalVar(cy.产品净收入,comy.产品净收入);
                    table.Cell(currentrow, 4).Shape.TextFrame.TextRange.Text = comp.ToString("p");
                    //写入产品Sales实际
                    table.Cell(currentrow, 5).Shape.TextFrame.TextRange.Text = cy.产品净收入.ToString("#,##0");
                    //写入产品SalesPlan
                    table.Cell(currentrow, 6).Shape.TextFrame.TextRange.Text = cyplan.产品净收入.ToString("#,##0");
                    //写入产品SalesVar%
                    comp = Calculate.CalVar(cy.产品净收入, cyplan.产品净收入);
                    table.Cell(currentrow, 7).Shape.TextFrame.TextRange.Text = comp.ToString("p");
                    BorderHelper.SetRightBorder(table.Cell(currentrow, 7));
                    //写入非产品Vs19Comps
                    comp = Calculate.CalVar(cy.非产品净收入, comy.非产品净收入);
                    table.Cell(currentrow, 8).Shape.TextFrame.TextRange.Text = comp.ToString("p");
                    //写入非产品Sales实际
                    table.Cell(currentrow, 9).Shape.TextFrame.TextRange.Text = cy.非产品净收入.ToString("#,##0");
                    //写入非产品SalesPlan
                    table.Cell(currentrow, 10).Shape.TextFrame.TextRange.Text = cyplan.非产品净收入.ToString("#,##0");
                    //写入非产品SalesVar%
                    comp = Calculate.CalVar(cy.非产品净收入, cyplan.非产品净收入);
                    table.Cell(currentrow, 11).Shape.TextFrame.TextRange.Text = comp.ToString("p");
                    BorderHelper.SetRightBorder(table.Cell(currentrow, 11));
                    //写入餐厅现金实际
                    table.Cell(currentrow, 12).Shape.TextFrame.TextRange.Text = cy.餐厅现金流.ToString("#,##0");
                    //写入餐厅现金流Plan
                    table.Cell(currentrow, 13).Shape.TextFrame.TextRange.Text = cyplan.餐厅现金流.ToString("#,##0");
                    //写入餐厅现金流Var%
                    comp = Calculate.CalVar(cy.餐厅现金流, cyplan.餐厅现金流);
                    table.Cell(currentrow, 14).Shape.TextFrame.TextRange.Text = comp.ToString("p");
                    BorderHelper.SetRightBorder(table.Cell(currentrow, 14));
                    //写入再投资
                    table.Cell(currentrow, 15).Shape.TextFrame.TextRange.Text = cy.再投资.ToString("#,##0");
                    BorderHelper.SetRightBorder(table.Cell(currentrow, 15));
                    //写入债前现金实际
                    table.Cell(currentrow, 16).Shape.TextFrame.TextRange.Text = cy.债前现金流.ToString("#,##0");
                    //写入债前现金流Plan
                    table.Cell(currentrow, 17).Shape.TextFrame.TextRange.Text = cyplan.债前现金流.ToString("#,##0");
                    //写入债前现金流Var%
                    comp = Calculate.CalVar(cy.债前现金流, cyplan.债前现金流);
                    table.Cell(currentrow, 18).Shape.TextFrame.TextRange.Text = comp.ToString("p");
                    //
                    currentrow++;
                }
            }
            //结束写入数据
            //整理格式
            Common.AutoFit(table);
            Common.OrganizeFormat(table);
            tableShape.Width = currentslide.Master.Width;
            tableShape.Height = currentslide.Master.Height - 96;            
        } 
    }
}
